package cn.com.yusys.yusp.custManager.utils;


import cn.com.yusys.yusp.custManager.domain.OcrmFCiFqDbcol;
import cn.com.yusys.yusp.uaa.security.AuthUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 灵活查询SQL生成器，单态模式，线程非安全
 *
 * @author zhengwei3
 * @since 2020-11-9
 */
public class FlexibleQueryUtils {
    private final static Logger logger = LoggerFactory.getLogger(FlexibleQueryUtils.class);
    /**
     * 条件数据操作符
     */
    public static final Map<String, String> conditonOperates = new HashMap<String, String>();

    static {
        conditonOperates.put("0000", "包含");
        conditonOperates.put("0001", "大于");
        conditonOperates.put("0002", "等于");
        conditonOperates.put("0003", "小于");
        conditonOperates.put("0004", "大于等于");
        conditonOperates.put("0005", "小于等于");
        conditonOperates.put("0006", "不等于");
    }

    public static final String OP_INCLUDE = "0000";
    public static final String OP_LARGER = "0001";
    public static final String OP_EQUAL = "0002";
    public static final String OP_SMALLER = "0003";
    public static final String OP_E_LARGER = "0004";
    public static final String OP_E_SAMLLER = "0005";
    public static final String OP_NO_EQUAL = "0006";


    /**
     * 连表类型
     */
    public static final Map<String, String> joinType = new HashMap<String, String>();

    static {
        joinType.put("left", "LEFT JOIN");
        joinType.put("right", "RIGHT JOIN");
        joinType.put("inner", "INNER JOIN");
    }

    /**
     * 客户基本信息表
     */
    public static final String CUST_MAIN_INFO_TABLE = "ACRM_F_CI_CUSTOMER";

    /**
     * 条件连接符
     */
    public static final String OR = "OR";
    public static final String AND = "AND";

    public static final String LEFTBRACKET = "(";
    public static final String RIGHTBRACKET = ")";

    /**
     * 数据类型列表
     */
    public static final List<String> dataTypes = new ArrayList<String>();

    static {
        dataTypes.add("VARCHAR2");//0
        dataTypes.add("DATE");//1
        dataTypes.add("NUMBER");//2
        dataTypes.add("DECIMAL");//3
        dataTypes.add("INTEGER");//4
        dataTypes.add("VARCHAR");//5
        dataTypes.add("CHAR");//6
        dataTypes.add("INT");//7
        dataTypes.add("DATETIME");//8
        dataTypes.add("BIGINT");//9
        dataTypes.add("NVARCHAR2");//10

    }


    /**
     * 1-汇总数值
     * 2-汇总数量
     * 3-最大值
     * 4-最小值
     * 5-总值
     * 6-平均值
     * 汇总类型列表
     */
    public static final List<String> sumTypes = new ArrayList<String>();

    static {
        sumTypes.add("1");
        sumTypes.add("2");
        sumTypes.add("3");
        sumTypes.add("4");
        sumTypes.add("5");
        sumTypes.add("6");
        sumTypes.add("7");
    }

    /**
     * 排序类型
     */
    public static final String ASC = "ASC";
    public static final String DESC = "DESC";
    // 分隔符
    public static final String seporator = ",";

    /**
     * 创建查询SELECT 子句
     *
     * @param ：查询结果涉及的数据集对象
     * @param dscs          ： 查询结果列对象
     * @param jaColumns     ：结果列属性，用于别名生成
     * @return
     */
    private static final String createSelectColumnsData(List<OcrmFCiFqDbcol> dscs, JSONArray jaColumns) {

        StringBuilder namesb = new StringBuilder();
        String CUST_NAME_BASE = "CUST_NAME";
        namesb.append("  case  when length(" + CUST_NAME_BASE + ") = 2 then subStr(" + CUST_NAME_BASE + ",0,1) || '*' ");
        namesb.append("when length(" + CUST_NAME_BASE + ") = 3 then subStr(" + CUST_NAME_BASE + ",0,1) || '*' || subStr(" + CUST_NAME_BASE + ",3,4) ");
        namesb.append("when length(" + CUST_NAME_BASE + ") = 4 then subStr(" + CUST_NAME_BASE + ",0,1) || '**' || subStr(" + CUST_NAME_BASE + ",4, 5 ) ");
        namesb.append("when length(" + CUST_NAME_BASE + ") = 5 then subStr(" + CUST_NAME_BASE + ",0,2) || '**' || subStr(" + CUST_NAME_BASE + ",4, 6 ) ");
        namesb.append("when length(" + CUST_NAME_BASE + ") > 5 then subStr(" + CUST_NAME_BASE + ",0, length(" + CUST_NAME_BASE + ") /2-2) || '****' || subStr(" + CUST_NAME_BASE + ",length(" + CUST_NAME_BASE + ") /2+3, length(" + CUST_NAME_BASE + ") )");
        namesb.append("else " + CUST_NAME_BASE + " ");
        namesb.append(" END ");

        String select = "CUST_NO as CUST_ID_BASE ," + namesb.toString() + " as CUST_NAME_BASE,";
        if (!dscs.isEmpty()) {
            for (int i = 0; i < dscs.size(); i++) {
                JSONObject jo = (JSONObject) jaColumns.get(i);
                Long columnId = jo.getLong("columnId");
                OcrmFCiFqDbcol dsc = getJoinColumnById(dscs, columnId);
                //脱敏处理begin
                StringBuilder sb = new StringBuilder();
                if (dsc.getNoSensi() != null || !"".equals(dsc.getNoSensi())) {//脱敏处理
                    if ("1".equals(dsc.getNoSensi())) {//客户名称 联系方式
                        String CUST_NAME = dsc.getColNameE();
                        sb.append("  case  when length(" + CUST_NAME + ") = 2 then subStr(" + CUST_NAME + ",0,1) || '*' ");
                        sb.append("when length(" + CUST_NAME + ") = 3 then subStr(" + CUST_NAME + ",0,1) || '*' || subStr(" + CUST_NAME + ",3,4) ");
                        sb.append("when length(" + CUST_NAME + ") = 4 then subStr(" + CUST_NAME + ",0,1) || '**' || subStr(" + CUST_NAME + ",4, 5 ) ");
                        sb.append("when length(" + CUST_NAME + ") = 5 then subStr(" + CUST_NAME + ",0,2) || '**' || subStr(" + CUST_NAME + ",4, 6 ) ");
                        sb.append("when length(" + CUST_NAME + ") > 5 then subStr(" + CUST_NAME + ",0, length(" + CUST_NAME + ") /2-2) || '****' || subStr(" + CUST_NAME + ",length(" + CUST_NAME + ") /2+3, length(" + CUST_NAME + ") )");
                        sb.append("else " + CUST_NAME + " ");
                        sb.append(" END ");
                    } else if ("2".equals(dsc.getNoSensi())) {//证件号码
                        sb.append("subStr(" + dsc.getColNameE() + ", 0, length(" + dsc.getColNameE() + ") - 4) || '****'");
                    } else if ("3".equals(dsc.getNoSensi())) {//联系地址
                        String CUST_ADDR = dsc.getColNameE();
                        sb.append("  case  when length(" + CUST_ADDR + ") = 2 then subStr(" + CUST_ADDR + ",0,1) || '*' ");
                        sb.append("when length(" + CUST_ADDR + ") = 3 then subStr(" + CUST_ADDR + ",0,1) || '*' || subStr(" + CUST_ADDR + ",3,4) ");
                        sb.append("when length(" + CUST_ADDR + ") = 4 then subStr(" + CUST_ADDR + ",0,1) || '**' || subStr(" + CUST_ADDR + ",4,5 ) ");
                        sb.append("when length(" + CUST_ADDR + ") = 5 then subStr(" + CUST_ADDR + ",0,2) || '***' || subStr(" + CUST_ADDR + ",4,6 ) ");
                        sb.append("when length(" + CUST_ADDR + ") > 5 then subStr(" + CUST_ADDR + ",0,5) || '****' ");
                        sb.append("else " + CUST_ADDR + " ");
                        sb.append(" END ");
                    } else {
                        if (null != dsc.getNotes() && !dsc.getNotes().equals("")) {
                            sb.append("(select LOOKUP_ITEM_NAME FROM (select * from ADMIN_SM_LOOKUP_ITEM where LOOKUP_CODE='"
                                    + dsc.getNotes() + "') A WHERE A.LOOKUP_ITEM_CODE= " + dsc.getColNameE() + ")");
                        } else {
                            sb.append(dsc.getColNameE());
                        }
                        //sb.append( dsc.getColNameE());
                    }
                }
                //脱敏处理end
                String columnName = sb.toString()
                        + " AS " + dsc.getColNameE() + "_"
                        + jo.getString("columnTotle");
                if (i == 0) {
                    select += columnName;
                } else {
                    select += "," + columnName;
                }
            }
        } else {
            select = " CUST_ID as CUST_ID_BASE ,CUST_NAME as CUST_NAME_BASE ";
        }
        return select;
    }


    private static final String createSelectColumnsexportdata(JSONArray jaCondition, List<OcrmFCiFqDbcol> dscs, JSONArray jaColumns, Object page) {
        String select = "base_info.CUST_NO as CUST_ID_BASE ,base_info.CUST_NAME as CUST_NAME_BASE,base_info.GRID_ID,base_info.GRID_NAME,base_info.SECOND_GRID_ID,base_info.SECOND_GRID_NAME,base_info.USER_NO,base_info.USER_NAME";
        logger.info("dscs++++++" + dscs);
        logger.info("jaColumns++++++" + jaColumns);
        if (!dscs.isEmpty()) {
            for (int i = 1; i <= dscs.size(); i++) {
                // [{"sortType":"1","columnId":"1003","columnTotle":"0"},{"sortType":"1","columnId":"1002","columnTotle":"0"}]
                JSONObject jo = (JSONObject) jaColumns.get(i - 1);
                Long columnId = jo.getLong("columnId");
                OcrmFCiFqDbcol dsc = getJoinColumnById(dscs, columnId);
                String alias = "base_info";
                String columnName = "";

                if (null != dsc.getNotes() && !dsc.getNotes().equals("") && page != null) {//数据字典
                    columnName = " ,(select LOOKUP_ITEM_NAME FROM (select * from ADMIN_SM_LOOKUP_ITEM where LOOKUP_CODE='"
                            + dsc.getNotes() + "') A WHERE A.LOOKUP_ITEM_CODE= " + alias + "." + dsc.getColNameE()
                            + ") AS " + dsc.getColNameE();
                } else {
                    if ("3001".equals(String.valueOf(dsc.getId()))) {//区域
                        columnName = " ,belong_info.BELONG_AREA_NAME  AS " + dsc.getColNameE();
                    } else if ("3002".equals(String.valueOf(dsc.getId()))) {//团队
                        columnName = " ,belong_info.BELONG_TEAM_NAME  AS " + dsc.getColNameE();
                    } else if ("3003".equals(String.valueOf(dsc.getId()))) {//客户经理
                        columnName = " ,belong_info.BELONG_MGR_NAME  AS " + dsc.getColNameE();
                    } else {
                        columnName = "," + alias + "." + dsc.getColNameE()
                                + " AS " + dsc.getColNameE();
                    }
                }
                select += columnName;
            }
        }
        return select;
    }

    /**
     * 创建查询SELECT 子句
     *
     * @param ：查询结果涉及的数据集对象
     * @param jaColumns     ：结果列属性，用于别名生成
     * @return 根据分组参数和统计参数创建SELECT 子句
     */
    public static final String createSelectColumns(List<OcrmFCiFqDbcol> dscs, JSONArray jaColumns) {
        return createSelectColumnsData(dscs, jaColumns);
    }

    /**
     * 创建查询SELECT 子句  导出使用不做脱敏处理
     *
     * @param ：查询结果涉及的数据集对象
     * @param jaColumns     ：结果列属性，用于别名生成
     * @return 创建SELECT 子句
     */
    public static final String createSelectColumnsexport(JSONArray jaCondition, List<OcrmFCiFqDbcol> dscs, JSONArray jaColumns, Object page) {

        return createSelectColumnsexportdata(jaCondition, dscs, jaColumns, page);
    }

    /**
     * 创建查询条件语句
     *
     * @param ：               查询条件JSON对象
     * @param ：查询条件涉及的数据集对象
     * @param conditonsColumn ：查询条件涉及的列对象
     * @return
     */
    public static final String createWhereColumns(JSONArray condi, List<OcrmFCiFqDbcol> conditonsColumn, String mgrFlag, String orgId) {
        String condition = "";
        logger.info("condi:" + condi);//SS_COL_ITEM:101 ;SS_COL_VALUE -> 张;SS_COL_GJOIN -> and;SS_COL_ORDER -> 0;SS_COL_JOIN ->''; SS_COL_OP -> 0000;SS_COL_GORDER -> {Integer@14312} 0
        logger.info("conditonsColumn:" + conditonsColumn);//OcrmFCiFqDbcol{id=101, colNameE='CUST_NAME', colNameC='客户姓名', colSize=0, colType='NVARCHAR2', defaultValue='null', isNull='null', primaryKeyFlag='null', dbtableId=0, notes='null', isEnable='true', colSort=0, dbtableName='null', groupId=0, fieldType='1', fName='null', objId=1, alias='null', noSensi='null'}
        Map parsedTokenMap = AuthUtils.getParsedAccessTokenMap();
        String userId = AuthUtils.getCurrentUserLogin(parsedTokenMap);
        for (Object sc : condi) {
            //JSONArray scd = (JSONArray) sc;
            JSONArray scd = JSONArray.parseArray(JSON.toJSONString(sc));
            //添加外连接符
            String outerJoin = ((JSONObject) scd.get(0)).getString("SS_COL_GJOIN");

            if (!condition.equals("") && "and".equals(outerJoin)) {
                condition += " " + AND + " ";
            } else if (!condition.equals("") && "or".equals(outerJoin)) {
                condition += " " + OR + " ";
            }
            String innerCondi = "";

            for (Object scc : scd) {
                JSONObject sccd = (JSONObject) scc;
                Long columnId = sccd.getLong("SS_COL_ITEM");
                String op = sccd.getString("SS_COL_OP");
                String value = sccd.getString("SS_COL_VALUE");

                OcrmFCiFqDbcol dsc = getJoinColumnById(conditonsColumn, columnId);
                String conditionValue = getConditonString(op, value, dsc);
                logger.info("conditionValue:" + conditionValue);
                if (null == conditionValue) {
                    continue;
                }
                innerCondi += dsc.getColNameE() + conditionValue;
                condition += innerCondi;
            }
        }
        return condition;
    }

    /**
     * 创建排序ORDER BY 子句，
     *
     * @param ：涉及数据集
     * @param dscs      ：涉及列
     * @param jaColumns ：结果集数据属性，取排序类型
     * @return
     */
    public static String createOrderSQL(List<OcrmFCiFqDbcol> dscs, JSONArray jaColumns) {

        String returns = "";
        for (Object o : jaColumns) {
            JSONObject jo = (JSONObject) o;
            OcrmFCiFqDbcol dsc = getJoinColumnById(dscs, jo.getLong("columnId"));
            int orderType = 0;
            if (!"".equals(jo.getString("sortType")) && jo.getString("sortType") != null) {
                String orderTypes = jo.getString("sortType");
                orderType = Integer.parseInt(orderTypes);
            }
            String order = "";
            switch (orderType) {
                case 1:
                    break;
                case 2:
                    order = dsc.getColNameE() + " " + ASC;
                    break;
                case 3:
                    order = dsc.getColNameE() + " " + DESC;
                    break;
                default:
                    break;
            }
            if (order.equals("")) {
                continue;
            }

            if (returns.equals("")) {
                returns = order;
            } else {
                returns += "," + order;
            }
        }

        return returns;
    }

    /**
     * 创建一个条件子句
     *
     * @param op    ：操作符
     * @param value ：条件值
     * @param dsc   ：涉及列
     * @return
     */
    private static String getConditonString(String op, String value, OcrmFCiFqDbcol dsc) {

        String columnType = dsc.getColType().trim();//加上trim，容错处理
        logger.info("dsc：" + dsc);
        String result = "";

        if (op.equals(OP_INCLUDE)) {

            switch (dataTypes.indexOf(columnType)) {
                case 6:
                case 5:
                case 10:
                case 0:
                    result += " LIKE '%" + value + "%'";
                    break;
                case 1:
                case 2:
                case 9:
                    return null;
                case 3:
                    return null;
                case 4:
                    return null;
                case 7:
                    return null;
                default:
            }
        } else if (op.equals(OP_LARGER)) {
            switch (dataTypes.indexOf(columnType)) {
                case 6:
                case 5:
                case 10:
                case 0:
                case 1:
                case 8:
                    result += " > " + "'" + value + "'";
                    break;
                case 2:
                case 3:
                case 4:
                case 7:
                    result += " > " + value;
                    break;
                default:
            }
        } else if (op.equals(OP_E_SAMLLER)) {//0005 小于等于
            switch (dataTypes.indexOf(columnType)) {
                case 6:
                case 5:
                case 10:
                case 0:
                case 1:
                case 8:
                    result += " <= " + "'" + value + "'";
                    break;
                case 2:
                case 3:
                case 4:
                case 7:
                    result += " <= " + value;
                    break;
                default:
            }
        } else if (op.equals(OP_SMALLER)) {
            switch (dataTypes.indexOf(columnType)) {
                case 6:
                case 5:
                case 10:
                case 0:
                case 1:
                case 8:
                    result += " < " + "'" + value + "'";
                    break;
                case 2:
                case 3:
                case 4:
                case 7:
                    result += " < " + value;
                    break;
                default:
            }

        } else if (op.equals(OP_E_LARGER)) {

            switch (dataTypes.indexOf(columnType)) {
                case 6:
                case 5:
                case 10:
                case 0:
                case 1:
                case 8:
                    result += " >= " + "'" + value + "'";
                    break;
                case 2:
                case 3:
                case 4:
                case 7:
                    result += " >= " + value;
                    break;
                default:
            }
        } else if (op.equals(OP_NO_EQUAL)) {

            switch (dataTypes.indexOf(columnType)) {
                case 6:
                case 5:
                case 10:
                case 0:
                case 1:
                case 8:
                    result += " <> " + "'" + value + "'";
                    break;
                case 2:
                case 3:
                case 4:
                case 7:
                    result += " <> " + value;
                    break;
                default:
            }
        } else {
            switch (dataTypes.indexOf(columnType)) {
                case 6:
                case 5:
                case 10:
                case 0:
                case 1:
                case 8:
                    result += " = '" + value + "'";
                    break;
                case 2:
                    result += " = " + value;
                    break;
                case 3:
                case 4:
                case 7:
                    result += " = " + value;
                    break;
                default:
            }
        }

        return result;
    }

    /**
     * 根据ID，从数组中获取数据列对象
     *
     * @param joinColumns
     * @param id
     * @return
     */
    private static OcrmFCiFqDbcol getJoinColumnById(List<OcrmFCiFqDbcol> joinColumns, Long id) {
        for (OcrmFCiFqDbcol column : joinColumns) {
            Long ids = column.getId();
            if (ids.equals(id)) {
                return column;
            }
        }
        return null;
    }

}
